const { DataTypes } = require('sequelize');

/**
 * 创建推荐内容和用户成就相关的数据库表
 */
module.exports = {
  async up(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    
    try {
      console.log('🏗️  开始创建推荐内容和成就相关表...');

      // 1. 推荐内容表
      await queryInterface.createTable('recommendations', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '推荐内容ID'
        },
        title: {
          type: DataTypes.STRING(200),
          allowNull: false,
          comment: '标题'
        },
        description: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '描述'
        },
        cover: {
          type: DataTypes.STRING(500),
          allowNull: true,
          comment: '封面图片URL'
        },
        rating: {
          type: DataTypes.DECIMAL(3, 2),
          defaultValue: 0,
          comment: '评分'
        },
        study_count: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '学习人数'
        },
        type: {
          type: DataTypes.ENUM('course', 'article', 'video', 'audio'),
          allowNull: false,
          comment: '内容类型'
        },
        duration: {
          type: DataTypes.INTEGER,
          allowNull: true,
          comment: '时长(秒)'
        },
        difficulty: {
          type: DataTypes.ENUM('beginner', 'intermediate', 'advanced'),
          defaultValue: 'beginner',
          comment: '难度级别'
        },
        category_id: {
          type: DataTypes.INTEGER,
          allowNull: true,
          comment: '分类ID'
        },
        instructor: {
          type: DataTypes.STRING(100),
          allowNull: true,
          comment: '讲师'
        },
        price: {
          type: DataTypes.DECIMAL(10, 2),
          defaultValue: 0,
          comment: '价格'
        },
        original_price: {
          type: DataTypes.DECIMAL(10, 2),
          defaultValue: 0,
          comment: '原价'
        },
        tags: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '标签'
        },
        content_url: {
          type: DataTypes.STRING(500),
          allowNull: true,
          comment: '内容链接'
        },
        sort: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '排序'
        },
        is_featured: {
          type: DataTypes.BOOLEAN,
          defaultValue: false,
          comment: '是否精选'
        },
        is_active: {
          type: DataTypes.BOOLEAN,
          defaultValue: true,
          comment: '是否启用'
        },
        view_count: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '查看次数'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '推荐内容表'
      });

      // 2. 用户成就表
      await queryInterface.createTable('user_achievements', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '用户成就ID'
        },
        user_id: {
          type: DataTypes.INTEGER,
          allowNull: false,
          comment: '用户ID'
        },
        achievement_key: {
          type: DataTypes.STRING(100),
          allowNull: false,
          comment: '成就键值'
        },
        achievement_name: {
          type: DataTypes.STRING(100),
          allowNull: false,
          comment: '成就名称'
        },
        description: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '成就描述'
        },
        icon: {
          type: DataTypes.STRING(200),
          allowNull: true,
          comment: '成就图标'
        },
        badge_color: {
          type: DataTypes.STRING(20),
          allowNull: true,
          comment: '徽章颜色'
        },
        points: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '获得积分'
        },
        experience: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '获得经验'
        },
        earned_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW,
          comment: '获得时间'
        },
        progress: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '进度数据'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '用户成就表'
      });

      // 3. 成就定义表
      await queryInterface.createTable('achievement_definitions', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '成就定义ID'
        },
        key: {
          type: DataTypes.STRING(100),
          allowNull: false,
          unique: true,
          comment: '成就键值'
        },
        name: {
          type: DataTypes.STRING(100),
          allowNull: false,
          comment: '成就名称'
        },
        description: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '成就描述'
        },
        icon: {
          type: DataTypes.STRING(200),
          allowNull: true,
          comment: '成就图标'
        },
        badge_color: {
          type: DataTypes.STRING(20),
          allowNull: true,
          comment: '徽章颜色'
        },
        category: {
          type: DataTypes.STRING(50),
          allowNull: true,
          comment: '成就分类'
        },
        type: {
          type: DataTypes.ENUM('count', 'score', 'time', 'streak', 'special'),
          allowNull: false,
          comment: '成就类型'
        },
        target_value: {
          type: DataTypes.INTEGER,
          allowNull: true,
          comment: '目标值'
        },
        points: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '奖励积分'
        },
        experience: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '奖励经验'
        },
        conditions: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '达成条件'
        },
        sort: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '排序'
        },
        is_active: {
          type: DataTypes.BOOLEAN,
          defaultValue: true,
          comment: '是否启用'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '成就定义表'
      });

      // 4. 用户课程进度表
      await queryInterface.createTable('user_course_progress', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '课程进度ID'
        },
        user_id: {
          type: DataTypes.INTEGER,
          allowNull: false,
          comment: '用户ID'
        },
        course_id: {
          type: DataTypes.INTEGER,
          allowNull: false,
          comment: '课程ID'
        },
        progress: {
          type: DataTypes.DECIMAL(5, 2),
          defaultValue: 0,
          comment: '进度百分比'
        },
        current_lesson: {
          type: DataTypes.INTEGER,
          defaultValue: 1,
          comment: '当前课时'
        },
        total_lessons: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '总课时数'
        },
        study_time: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '学习时长(秒)'
        },
        last_study_at: {
          type: DataTypes.DATE,
          allowNull: true,
          comment: '最后学习时间'
        },
        is_completed: {
          type: DataTypes.BOOLEAN,
          defaultValue: false,
          comment: '是否完成'
        },
        completed_at: {
          type: DataTypes.DATE,
          allowNull: true,
          comment: '完成时间'
        },
        rating: {
          type: DataTypes.DECIMAL(3, 2),
          allowNull: true,
          comment: '用户评分'
        },
        review: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '用户评价'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '用户课程进度表'
      });

      // 添加外键约束
      await queryInterface.addConstraint('recommendations', {
        fields: ['category_id'],
        type: 'foreign key',
        name: 'fk_recommendations_category_id',
        references: {
          table: 'course_categories',
          field: 'id'
        },
        onDelete: 'SET NULL',
        onUpdate: 'CASCADE',
        transaction
      });

      await queryInterface.addConstraint('user_achievements', {
        fields: ['user_id'],
        type: 'foreign key',
        name: 'fk_user_achievements_user_id',
        references: {
          table: 'users',
          field: 'id'
        },
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        transaction
      });

      await queryInterface.addConstraint('user_course_progress', {
        fields: ['user_id'],
        type: 'foreign key',
        name: 'fk_user_course_progress_user_id',
        references: {
          table: 'users',
          field: 'id'
        },
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        transaction
      });

      await queryInterface.addConstraint('user_course_progress', {
        fields: ['course_id'],
        type: 'foreign key',
        name: 'fk_user_course_progress_course_id',
        references: {
          table: 'courses',
          field: 'id'
        },
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        transaction
      });

      // 添加索引
      await queryInterface.addIndex('recommendations', ['type', 'is_active'], {
        name: 'idx_recommendations_type_active',
        transaction
      });

      await queryInterface.addIndex('user_achievements', ['user_id', 'achievement_key'], {
        name: 'idx_user_achievements_user_key',
        unique: true,
        transaction
      });

      await queryInterface.addIndex('user_course_progress', ['user_id', 'course_id'], {
        name: 'idx_user_course_progress_user_course',
        unique: true,
        transaction
      });

      console.log('✅ 推荐内容和成就相关表创建完成');
      await transaction.commit();

    } catch (error) {
      await transaction.rollback();
      console.error('❌ 创建推荐内容和成就表失败:', error);
      throw error;
    }
  },

  async down(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    
    try {
      console.log('🗑️  开始删除推荐内容和成就相关表...');

      await queryInterface.dropTable('user_course_progress', { transaction });
      await queryInterface.dropTable('achievement_definitions', { transaction });
      await queryInterface.dropTable('user_achievements', { transaction });
      await queryInterface.dropTable('recommendations', { transaction });

      console.log('✅ 推荐内容和成就相关表删除完成');
      await transaction.commit();

    } catch (error) {
      await transaction.rollback();
      console.error('❌ 删除推荐内容和成就表失败:', error);
      throw error;
    }
  }
};
